package com.community.sqlapp.service;

import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
@RequiredArgsConstructor
public class DatabaseService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * 获取所有数据库表名
     */
    public List<String> getAllTables() {
        try {
            // 尝试不同的SQL查询方式
            String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()";
            List<String> tables = jdbcTemplate.queryForList(sql, String.class);

            // 添加日志
            System.out.println("找到的表: " + tables);
            return tables;
        } catch (Exception e) {
            e.printStackTrace();
            // 出错时尝试回退到原始方法
            try {
                return jdbcTemplate.queryForList("SHOW TABLES", String.class);
            } catch (Exception ex) {
                ex.printStackTrace();
                return new ArrayList<>();
            }
        }
    }

    /**
     * 获取表结构信息
     */
    public List<Map<String, Object>> getTableStructure(String tableName) {
        String sql = "DESCRIBE " + tableName;
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 执行SQL查询并返回结果
     */
    public Map<String, Object> executeQuery(String sql) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (sql.trim().toLowerCase().startsWith("select") || 
                sql.trim().toLowerCase().startsWith("show") || 
                sql.trim().toLowerCase().startsWith("describe")) {
                List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);
                
                // 获取列名
                Set<String> columnNames = new LinkedHashSet<>();
                if (!data.isEmpty()) {
                    columnNames.addAll(data.get(0).keySet());
                }
                
                result.put("success", true);
                result.put("data", data);
                result.put("columns", columnNames);
                result.put("rowCount", data.size());
            } else {
                int rowsAffected = jdbcTemplate.update(sql);
                result.put("success", true);
                result.put("rowsAffected", rowsAffected);
                result.put("message", "执行成功，影响了 " + rowsAffected + " 行数据");
            }
        } catch (Exception e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        return result;
    }
} 